IF exists(SELECT * FROM dbo.sysobjects WHERE name='CLBoktober' AND xtype='P') 
BEGIN
	DROP PROCEDURE CLBoktober
END
GO

CREATE procedure CLBoktober
as
set nocount on

create table #tussen
(inschrijvingsNr char(7),
achternaam varchar(50),
voornaam varchar(50))

insert into #tussen(inschrijvingsNr,achternaam,voornaam)
select distinct c.inschrijvingsNr,l.achternaam,l.voornaam 
from loopbaan as c
left join alleGestarteLln as l
  on l.inschrijvingsNr = c.inschrijvingsNr
where (left(c.klas,1)='1') and (c.jaar='2001')

select t.*,
lo.dw4 as loDW,lo.ex4 as loEX,
po.dw4 as poDW,po.ex4 as poEX,
mo.dw4 as moDW,mo.ex4 as moEX,
go.dw4 as goDW,go.ex4 as goEX,
ge.dw4 as geDW,ge.ex4 as geEX,
ne.dw4 as neDW,ne.ex4 as neEX,
fr.dw4 as frDW,fr.ex4 as frEX,
la.dw4 as laDW,la.ex4 as laEX,
wi.dw4 as wiDW,wi.ex4 as wiEX,
bi.dw4 as biDW,bi.ex4 as biEX,
aa.dw4 as aaDW,aa.ex4 as aaEX,
tov.dw4 as toDW,tov.ex4 as toEX,
ato.dw4 as atDW,ato.ex4 as atEX

from #tussen as t
  left join (select inschrijvingsNr,dw4,ex4 from loopbaan 
     where (left(klas,1)='1') 
	       and (jaar='2001') 
                 and vakcode = 'lo') as lo
     on t.inschrijvingsNr = lo.inschrijvingsNr
  left join (select inschrijvingsNr,dw4,ex4 from loopbaan 
     where (left(klas,1)='1') 
	       and (jaar='2001') 
                 and vakcode = 'po') as po
     on t.inschrijvingsNr = po.inschrijvingsNr
  left join (select inschrijvingsNr,dw4,ex4 from loopbaan 
     where (left(klas,1)='1') 
	       and (jaar='2001') 
                 and vakcode = 'mo') as mo
     on t.inschrijvingsNr = mo.inschrijvingsNr
  left join (select inschrijvingsNr,dw4,ex4 from loopbaan 
     where (left(klas,1)='1') 
	       and (jaar='2001') 
                 and vakcode = 'go') as 